﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class App_Form_hs_ds_sinhvien_nhap_excel : System.Web.UI.Page
{
    OleDbConnection oledbConn;
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void In_Click(object sender, EventArgs e)
    {
        GenerateExcelData();
    }
    private void GenerateExcelData()
    {
        try
        {
            // need to pass relative path after deploying on server
            string path = System.IO.Path.GetFullPath(Server.MapPath("~/InformationNew.xlsx"));
            /* connection string  to work with excel file. HDR=Yes - indicates 
               that the first row contains columnnames, not data. HDR=No - indicates 
               the opposite. "IMEX=1;" tells the driver to always read "intermixed" 
               (numbers, dates, strings etc) data columns as text. 
            Note that this option might affect excel sheet write access negative. */

            if (Path.GetExtension(path) == ".xls")
            {
                oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
            }
            else if (Path.GetExtension(path) == ".xlsx")
            {
                oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
            }
            oledbConn.Open();
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            DataSet ds = new DataSet();

            // passing list to drop-down list

            // selecting distict list of Slno 

            cmd.Connection = oledbConn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT [STT],[idlop],[HoTen] FROM [Sheet1$]";
            oleda = new OleDbDataAdapter(cmd);
            oleda.Fill(ds);

            // binding form data with grid view
            grvData.DataSource = ds.Tables[0].DefaultView;

            wdkhp abc = new wdkhp();
            abc.Fs_SINHVIEN_NH_EXCEL(ds.Tables[0]);
            
            grvData.DataBind();
        }
        // need to catch possible exceptions
        catch (Exception ex)
        {
            lblError.Text = ex.ToString();
        }
        finally
        {
            oledbConn.Close();
        }
    }
}